package cn.xerllent.wxtp.service.mapper;

import java.util.List;

import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import cn.xerllent.config.MybatisRedisCache;
import cn.xerllent.wxtp.entity.Dksb;
import cn.xerllent.wxtp.service.dto.DksbFilter;


@Repository
@Mapper
@CacheNamespace(implementation = MybatisRedisCache.class, eviction = MybatisRedisCache.class)
public interface DksbMapper extends BaseMapper<Dksb> {
    /**
     * 联表查询参数，结果集不包含DksbCourse记录，需要联表查询
     * */
    @Options(useCache = false)
    @Select("<script>"
    		+ "SELECT d.* FROM `wxtp_dksb` d left outer join `wxtp_dksb_course` c on d.id=c.dksb_id"
			+ "<where>"
			+ "<if test = \"corpId!=null\" > AND d.corp_id = #{corpId} </if>"
    		+ "<if test = \"blurry!=null and blurry!=''\" >"
    		+ " AND ("
	    		+ "d.comment LIKE concat('%', #{blurry}, '%') "
    		+ ")"
    		+ "</if>"
    		+ "<if test = \"status!=null and status!=''\" > AND d.status = #{status} </if>"
			+ "<trim prefix=\"AND (\" suffix=\")\" prefixOverrides=\"OR\">"
	    		+ "<if test = \"reqUserid!=null and reqUserid!=''\" > OR d.req_userid = #{reqUserid} </if>"
	    		+ "<if test = \"srcUserid!=null and srcUserid!=''\" > OR c.src_userid = #{srcUserid} </if>"
	    		+ "<if test = \"tarUserid!=null and tarUserid!=''\" > OR c.tar_userid = #{tarUserid} </if>"
    		+ "</trim>"
    		+ "<if test = \"grade!=null and grade!=''\" > AND c.grade LIKE concat(#{grade}, '%')  </if>"
    		+ "<if test = \"clasz!=null and clasz!=''\" > AND c.clasz = #{clasz} </if>"
    		+ "<if test = \"coursetime!=null and coursetime!=''\" > AND c.coursetime = #{coursetime} </if>"
    		+ "<if test = \"subject!=null and subject!=''\" > AND c.subject = #{subject} </if>"
    		
    		+ "<if test = \"createtimeFrom !=null\" > "
    			+ " AND d.createtime &gt;= #{createtimeFrom}"
    		+ "</if>"
    		+ "<if test = \"createtimeTo !=null\" > "
				+ " AND d.createtime &lt; #{createtimeTo}"
			+ "</if>"

    		+ "<if test = \"eventdateFrom !=null\" > "
				+ " AND c.eventdate &gt;= #{eventdateFrom}"
			+ "</if>"
			+ "<if test = \"eventdateTo !=null\" > "
				+ " AND c.eventdate &lt; #{eventdateTo}"
			+ "</if>"
    		+ "</where>"
    		+ " group by d.id "
    		//+ " order by u.id asc"
    		+ "</script>")
    List<Dksb> queryWithExt(DksbFilter filter);
    
    /**
     * 联表查询一对多注解方式,，结果集包含DksbCourse记录
     * */
    @Results({
    	@Result(column="id",property="id"),
    	@Result(column="corp_id",property="corpId"),
    	@Result(column="req_userid",property="reqUserid"),
    	@Result(column="comment",property="comment"),
    	@Result(column="third_no",property="thirdNo"),
    	@Result(column="template_id",property="templateId"),
    	@Result(column="status",property="status"),
    	@Result(column="createtime",property="createtime"),
    	@Result(column="updatetime",property="updatetime"),
    	@Result(column="audittime",property="audittime"),
    	@Result(column="id",property="courses",
    		many= @Many(
    				select="cn.xerllent.wxtp.service.mapper.DksbCourseMapper.getListByDksbId"
    		)
    	)
    })
    @Options(useCache = false)
    @Select("<script>"
    		+ "SELECT d.* FROM `wxtp_dksb` d left outer join `wxtp_dksb_course` c on d.id=c.dksb_id"
			+ "<where>"
			+ "<if test = \"corpId!=null\" > AND d.corp_id = #{corpId} </if>"
    		+ "<if test = \"blurry!=null and blurry!=''\" >"
    		+ " AND ("
	    		+ "d.comment LIKE concat('%', #{blurry}, '%') "
    		+ ")"
    		+ "</if>"
    		+ "<if test = \"status!=null\" > AND d.status = #{status} </if>"
    		
			+ "<trim prefix=\"AND (\" suffix=\")\" prefixOverrides=\"OR\">"
	    		+ "<if test = \"reqUserid!=null and reqUserid!=''\" > OR d.req_userid = #{reqUserid} </if>"
	    		+ "<if test = \"srcUserid!=null and srcUserid!=''\" > OR c.src_userid = #{srcUserid} </if>"
	    		+ "<if test = \"tarUserid!=null and tarUserid!=''\" > OR c.tar_userid = #{tarUserid} </if>"
			+ "</trim>"

    		+ "<if test = \"grade!=null and grade!=''\" > AND c.grade LIKE concat(#{grade}, '%') </if>"
    		+ "<if test = \"clasz!=null and clasz!=''\" > AND c.clasz = #{clasz} </if>"
    		+ "<if test = \"coursetime!=null and coursetime!=''\" > AND c.coursetime = #{coursetime} </if>"
    		+ "<if test = \"subject!=null and subject!=''\" > AND c.subject = #{subject} </if>"
    		
//    		+ "<if test = \"createtime !=null and createtime.size()>1\" > "
//    			+ " AND d.createtime BETWEEN  #{createtime[0]} and #{createtime[1]}"
//    		+ "</if>"
//    		+ "<if test = \"eventdate !=null and eventdate.size()>1\" > "
//				+ " AND c.eventdate BETWEEN  #{eventdate[0]} and #{eventdate[1]}"
//			+ "</if>"
				
    		+ "<if test = \"createtimeFrom !=null\" > "
				+ " AND d.createtime &gt;= #{createtimeFrom}"
			+ "</if>"
			+ "<if test = \"createtimeTo !=null\" > "
				+ " AND d.createtime &lt; #{createtimeTo}"
			+ "</if>"
	
			+ "<if test = \"eventdateFrom !=null\" > "
				+ " AND c.eventdate &gt;= #{eventdateFrom}"
			+ "</if>"
			+ "<if test = \"eventdateTo !=null\" > "
				+ " AND c.eventdate &lt; #{eventdateTo}"
			+ "</if>"
			
    		+ "</where>"
    		+ " group by d.id "
    		+ " order by d.id desc"
    		+ "</script>")
    List<Dksb> queryWithCourse(DksbFilter filter);
}